package com.tuanzi.loan.business.repository.galaxycard.impl;

import static com.tuanzi.loan.generated.jooq.loan.tables.GalaxyCardOrder.GALAXY_CARD_ORDER;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.stream.Collectors;

import org.jooq.Record;
import org.jooq.RecordMapper;
import org.jooq.impl.DSL;
import org.jooq.impl.TableImpl;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.Pageable;
import org.springframework.stereotype.Repository;

import com.tuanzi.loan.business.repository.galaxycard.custom.GalaxyCardAppPromoteStatCustomRepository;
import com.tuanzi.loan.core.repository.JOOQBaseRepository;
import com.tuanzi.loan.vo.request.galaxycard.GalaxyCardAppPromoteStatRequest;
import com.tuanzi.loan.vo.response.galaxycard.GalaxyCardAppPromoteStatResponse;
import com.tuanzi.server.loan_base.util.DateUtils;

@SuppressWarnings({ "rawtypes" })
@Repository
public class GalaxyCardAppPromoteStatRepositoryImpl extends JOOQBaseRepository implements GalaxyCardAppPromoteStatCustomRepository{
	
	@Override
	protected TableImpl getTable() {
		return GALAXY_CARD_ORDER;
	}

	@Override
	public Page<GalaxyCardAppPromoteStatResponse> list(
			GalaxyCardAppPromoteStatRequest searchRequest, Pageable pageable) {
		
		List<Object> paramList = new ArrayList<Object>();
		
		StringBuilder sql = new StringBuilder("SELECT a.create_date AS createDate, b.channel_code AS channelCode, b.channel_name AS channelName, IFNULL(c.promotePageViewUV, 0) AS promotePageViewUV, IFNULL(d.registerMobnumCount, 0) AS registerMobnumCount, IFNULL(e.newRegisterMobnumCount, 0) AS newRegisterMobnumCount, IFNULL(f.appActivateCount, 0) AS appActivateCount, IFNULL(g.createOrderCount, 0) AS createOrderCount, IFNULL(h.payOrderCount, 0) AS payOrderCount")
		.append(" FROM `stat`.`stat_date` AS a")
		.append(" INNER JOIN(")
		.append(" SELECT `channel_code`, channel_name FROM `loan`.`comm_product_channel` WHERE `prdid` = 8700 AND channel_code IN(?)")
		.append(" ) AS b")
		.append(" LEFT JOIN(")
		.append(" SELECT `create_date`, `channel_id`,  SUM(`uv`)  AS promotePageViewUV  FROM `loan`.`loan_galaxy_card_stat` WHERE `type` = 1 GROUP BY `create_date`,`channel_id` ")
		.append(" ) AS c ON a.`create_date` = c.create_date AND b.channel_code = c.channel_id")
		.append(" LEFT JOIN(")
		.append(" SELECT cdate, channel, COUNT(DISTINCT(phone)) AS registerMobnumCount FROM `loan`.`common_activity_register_record` WHERE prdid = 8701 GROUP BY cdate,channel")
		.append(" ) AS d ON a.`create_date` = d.cdate AND b.channel_code = d.channel")
		.append(" LEFT JOIN(")
		.append(" SELECT cdate, channel, COUNT(DISTINCT(phone)) AS newRegisterMobnumCount FROM `loan`.`common_activity_register_record` WHERE prdid = 8701 AND is_new_user = 1 GROUP BY cdate,channel")
		.append(" ) AS e ON a.`create_date` = e.cdate AND b.channel_code = e.channel")
		.append(" LEFT JOIN(")
		.append(" SELECT `date`, channel, COUNT(DISTINCT(phone_id)) AS appActivateCount FROM stat.stat_app_new WHERE `app_prd_id` = 8700 GROUP BY `date`, channel")
		.append(" ) AS f ON a.`create_date` = f.date AND b.channel_code = f.channel")
		.append(" LEFT JOIN(")
		.append(" SELECT DATE(create_time) AS cdate, channel, COUNT(DISTINCT(galaxy_card_id)) AS createOrderCount FROM `loan`.galaxy_card_order WHERE prd_id = 8700 GROUP BY cdate,channel")
		.append(" ) AS g ON a.`create_date` = g.cdate AND b.channel_code = g.channel")
		.append(" LEFT JOIN(")
		.append(" SELECT DATE(create_time) AS cdate, channel, COUNT(*) AS payOrderCount FROM `loan`.galaxy_card_order WHERE prd_id = 8700 AND order_status = 2 GROUP BY cdate,channel")
		.append(" ) AS h ON a.`create_date` = h.cdate AND b.channel_code = h.channel WHERE 1=1");
		
		if(searchRequest.getChannelCodeList() == null || searchRequest.getChannelCodeList().size() == 0){
			paramList.add("");
		}else{
			paramList.add(DSL.list(searchRequest.getChannelCodeList().stream().map(DSL::val).collect(Collectors.toList())));
		}
		
		if(searchRequest.getStartDate() != null){
			sql.append(" AND a.create_date >= ?");
			paramList.add(DateUtils.formatDateToYyyyMMddHHmmss(searchRequest.getStartDate()));
		}
		
		if(searchRequest.getEndDate() != null){
			sql.append(" AND a.create_date <= ?");
			paramList.add(DateUtils.formatDateToYyyyMMdd(searchRequest.getEndDate()) + " 23:59:59");
		}
		
		sql.append(" ORDER BY a.create_date DESC, b.channel_code");
		
		Long count = dslContext.fetchOne("SELECT COUNT(*) FROM (" + sql + ") AS i", paramList.toArray()).into(Long.class);
		
		paramList.add(pageable.getOffset());
		paramList.add(pageable.getPageSize());
		
		return new PageImpl<GalaxyCardAppPromoteStatResponse>(dslContext.fetch(sql.toString() + " LIMIT ?,? ", paramList.toArray()).map(new RecordMapper<Record, GalaxyCardAppPromoteStatResponse>() {

			@Override
			public GalaxyCardAppPromoteStatResponse map(Record record) {
				
				Date createDate = record.get("createDate", Date.class);
				String channelCode = record.get("channelCode", String.class);
				String channelName = record.get("channelName", String.class);
				Integer promotePageViewUV = record.get("promotePageViewUV", Integer.class);
				Integer registerMobnumCount = record.get("registerMobnumCount", Integer.class);
				Integer newRegisterMobnumCount = record.get("newRegisterMobnumCount", Integer.class);
				Integer appActivateCount = record.get("appActivateCount", Integer.class);
				Integer createOrderCount = record.get("createOrderCount", Integer.class);
				Integer payOrderCount = record.get("payOrderCount", Integer.class);
			    
				GalaxyCardAppPromoteStatResponse response = new GalaxyCardAppPromoteStatResponse();
				response.setCreateDate(createDate);
				response.setChannelCode(channelCode);
				response.setChannelName(channelName);
				response.setPromotePageViewUV(promotePageViewUV);
				response.setRegisterMobnumCount(registerMobnumCount);
				response.setNewRegisterMobnumCount(newRegisterMobnumCount);
				response.setAppActivateCount(appActivateCount);
				response.setCreateOrderCount(createOrderCount);
				response.setPayOrderCount(payOrderCount);
				
				return response;
			}
		}), pageable, count);
		
	}

	

}
